package edu.byu.intex2.data.dao;

import edu.byu.intex2.data.Cache;
import edu.byu.intex2.data.ConnectionPool;
import edu.byu.intex2.data.DataException;
import edu.byu.intex2.data.bo.Customer;
import edu.byu.intex2.data.bo.PhysicalCar;
import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

/**
 * PhysicalCar Data Access Object
 * @author Brady
 */
public class PhysicalCarDAO {

    /////////////////////////////////////////////
    ///   Singleton code
    private static PhysicalCarDAO instance = null;

    /** Creates a new instance of PhysicalCarDAO */
    private PhysicalCarDAO() {
    }//constructor

    /** Retrieves the single instance of this class */
    public static synchronized PhysicalCarDAO getInstance() {
        if (instance == null) {
            instance = new PhysicalCarDAO();
        }
        return instance;
    }//getInstance

    ////////////////////////////////////////////
    ///   CREATE methods
    /**
     * Creates a new physicalCar in the database
     * set alreadyInDB to false
     * put physicalCar in cache
     */
    public PhysicalCar create(String id) throws DataException {
        PhysicalCar physicalCar = new PhysicalCar(id);
        physicalCar.setObjectAlreadyInDB(false);
        Cache.getInstance().put(physicalCar.getId(), physicalCar);
        return physicalCar;
    }//create


    ////////////////////////////////////////////
    ///   READ methods
    /** Reads an existing physicalCar from the database     */
    public PhysicalCar read(String id) throws DataException {
        // Check Cache
        if (Cache.getInstance().containsKey(id)) {
            return (PhysicalCar) Cache.getInstance().get(id);
        }

        // Get Connection
        Connection conn = ConnectionPool.getInstance().get();

        // Try Reading with connection
        try {
            return read(id, conn);
        } catch (Exception e) {
            throw new DataException("An error occurred while reading the business object information.", e);
        } finally {
            ConnectionPool.getInstance().release(conn);
        }
    }

    /**
     * Internal method to read an existing physicalCar from the database
     * @return physicalCar
     */
    synchronized PhysicalCar read(String id, Connection conn) throws Exception {
        // Check Cache
        if (Cache.getInstance().containsKey(id)) {
            return (PhysicalCar) Cache.getInstance().get(id);
        }

        // Prepare a SQL Statement
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM physical_car where id=?");

        try {
            stmt.setString(1, id);
            ResultSet rs = stmt.executeQuery(); // execute query
            if (rs.next()) {
                // Read the record with the result set
                return readRecord(rs, conn);
            }//if
            throw new DataException("PhysicalCar with id '" + id + "' not found.");
        } finally {
            stmt.close();
        }
    }//read

    /** Internal method to create a physicalCar object from a record  */
    synchronized PhysicalCar readRecord(ResultSet rs, Connection conn) throws Exception {
        if (Cache.getInstance().containsKey(rs.getString("id"))) {
            return (PhysicalCar) Cache.getInstance().get(rs.getString("id"));
        }
        // Create new physicalCar object
        PhysicalCar physicalCar = new PhysicalCar(rs.getString("id"));

        // PhysicalCar is already in DB
        physicalCar.setObjectAlreadyInDB(true);

        // Put physicalCar in cache
        Cache.getInstance().put(physicalCar.getId(), physicalCar);

        // Set PhysicalCar Attributes
        physicalCar.setVin(rs.getString("vin"));
        physicalCar.setPrice(rs.getDouble("price"));
        physicalCar.setColor(rs.getString("color"));
        physicalCar.setConceptualCarId(rs.getString("cc_id"));

        // PhysicalCar is dirty and needs to be saved to DB
        physicalCar.setDirty(false);

        return physicalCar;
    }//readRecord

    /*
     * Get the Physical Car from the database based on the VIN
     * @param VIN of physical car to find.
     */
    public PhysicalCar getByVIN(String VIN) throws Exception {
        // Grab a connection
        Connection conn = ConnectionPool.getInstance().get();

        // use a prepared statement to prevent sql injection
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM physical_car WHERE vin=?");
        try {
            stmt.setString(1, VIN);
            ResultSet rs = stmt.executeQuery(); // execute query
            if (rs.next()) {
                // Read the record with the result set
                return readRecord(rs, conn);
            }//if
            throw new DataException("PhysicalCar with VIN '" + VIN + "' not found.");
        } finally {
            stmt.close();
            ConnectionPool.getInstance().release(conn);  // release the connection
        }
    }


    /////////////////////////////////////////////
    ///   UPDATE methods
    /** Saves an existing physicalCar in the database */
    public void save(PhysicalCar physicalCar) throws DataException {
        // get a connection from the pool, save all of the sales first using the other physicalCar method and then commit them in the database
        Connection conn = ConnectionPool.getInstance().get();

        try {
            save(physicalCar, conn); // save with connection
            conn.commit();  // commit the db transaction
        } catch (Exception e) {
            try {
                //rollback if there were any errors saving, catch any other errors you might have
                conn.rollback();
            } catch (SQLException e2) {
                throw new DataException("Could not roll back the physicalCar database transaction!", e2);
            }
            throw new DataException("An error occurred while saving the physicalCar object information.", e);
        } finally {
            // release the connection
            ConnectionPool.getInstance().release(conn);
        }
    }

    /** Internal method to update a physicalCar in the database */
    void save(PhysicalCar physicalCar, Connection conn) throws Exception {
        // put the physicalCar object to cache
        Cache.getInstance().put(physicalCar.getId(), physicalCar);

        // decide whether to update or insert into the database
        if (physicalCar.isObjectAlreadyInDB() && physicalCar.isDirty()) {
            update(physicalCar, conn);
        } else if (physicalCar.isDirty()) {
            insert(physicalCar, conn);
        }//if
    }

    /** External method to update or create a physicalCar in the database without committing connection */
    public void saveNoCommit(PhysicalCar physicalCar, Connection conn/*, Boolean alreadyInDB*/) throws Exception {
        Boolean alreadyInDB = physicalCar.isObjectAlreadyInDB();

        // decide whether to update or insert into the database
        if (alreadyInDB) {
            update(physicalCar, conn);
        } else {
            insert(physicalCar, conn);
        }
    }

    public Boolean isInDB(String id) throws Exception{
        Connection conn = ConnectionPool.getInstance().get();
        // use a prepared statement to prevent sql injection
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM physical_car WHERE id=?");
        try {
            // Get PhysicalCar Attributes and add them to the statement
            stmt.setString(1, id);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()){
                return true;  // found it
            }else{
                return false; // didn't find it
            }
        } finally {
            stmt.close();  // make sure to close the prepared statement
        }
    }

    /** Saves an existing physicalCar to the database */
    private void update(PhysicalCar physicalCar, Connection conn) throws Exception {
        // use a prepared statement to prevent sql injection
        PreparedStatement stmt = conn.prepareStatement("UPDATE physical_car SET vin=?, price=?, color=?, cc_id=?  WHERE id=?");
        try {
            // Get PhysicalCar Attributes and add them to the statement
            stmt.setString(1, physicalCar.getVin());
            stmt.setDouble(2, physicalCar.getPrice());
            stmt.setString(3, physicalCar.getColor());
            stmt.setString(4, physicalCar.getConceptualCarId());
            stmt.setString(5, physicalCar.getId());
            stmt.execute();

            // PhysicalCar is clean!
            physicalCar.setDirty(false);
        } finally {
            stmt.close();  // make sure to close the prepared statement
        }
    }

    /** Inserts a new physicalCar into the database */
    private void insert(PhysicalCar physicalCar, Connection conn) throws Exception {
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO physical_car (id, vin, price, color, cc_id) VALUES (?, ?, ?, ?, ?)");
        try {
            // Set PhysicalCar Attributes
            stmt.setString(1, physicalCar.getId());
            stmt.setString(2, physicalCar.getVin());
            stmt.setDouble(3, physicalCar.getPrice());
            stmt.setString(4, physicalCar.getColor());
            stmt.setString(5, physicalCar.getConceptualCarId());
            stmt.execute();

            physicalCar.setObjectAlreadyInDB(true); // now in DB
            physicalCar.setDirty(false); // object is clean
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            stmt.close(); // close the prepared statement
        }
    }

    /////////////////////////////////////////////////
    ///   DELETE methods
    /** We do not support deleting of business objects in this application */
    public void delete(PhysicalCar physicalCar) throws DataException {
        throw new RuntimeException("Nice try. The delete function is not supported in this application.");
    }

    ////////////////////////////////////////////////
    ///   SEARCH methods
    /** Retrieves all physicalCars in a list of arrays from the database */
    /** get physical car guid by vin
     * @param vin of car to find
     */
    public String getGuidByVin(String VIN) throws Exception{
        // Grab Connection
        Connection conn = ConnectionPool.getInstance().get();
        
        // Prepare a SQL Statement
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM physical_car where VIN=?");

        try {
            stmt.setString(1, VIN);
            ResultSet rs = stmt.executeQuery(); // execute query
            if (rs.next()) {
                // Return the id
                return rs.getString("id");
            }//if
            throw new DataException("GUID of Physical with VIN '" + VIN + "' not found.");
        } finally {
            stmt.close();
            ConnectionPool.getInstance().release(conn);
        }
    }

    /** Internal method to search by certain fields */
    private List<PhysicalCar> search(PreparedStatement stmt, Connection conn) throws Exception {
        List<PhysicalCar> pcs = new ArrayList<PhysicalCar>();

        try {
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                pcs.add(readRecord(rs, conn));
            }
        } finally {
            stmt.close();
            ConnectionPool.getInstance().release(conn);
        }

        return pcs;
    }

    /* Allow user to search by either first or last name or phone */
    public List<PhysicalCar> getPCsByCustomer(Customer customer) throws Exception {
        //establish connection and prepared statement
        Connection conn = ConnectionPool.getInstance().get();
        PreparedStatement stmt = conn.prepareStatement("SELECT p.id, p.vin, p.price, p.color, p.CC_ID FROM physical_car p, customer c, sale_line sl, sale s WHERE s.customer_id = c.id AND sl.sale_id = s.id AND sl.physical_car_id = p.id AND c.id =? ");

        stmt.setString(1, customer.getId());
        // run stmt through the search method, return a list of customers matching any name or phone number
        return search(stmt, conn);
    }

    ////////////////////////////////////////////////
    ///   Set methods
    /** sets all the employee values necessary */
    public PhysicalCar setPhysicalCarValues(PhysicalCar physicalCar, Connection conn) throws Exception {
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM physical_car where id=?");
        try {
            // Prevent SQL injection
            stmt.setString(1, physicalCar.getId());

            // Execute query
            ResultSet rs = stmt.executeQuery();

            // If we found a record, determine what to do.
            if (rs.next()) {
                // Set each attribute to object
                physicalCar.setVin(rs.getString("vin"));
                physicalCar.setPrice(rs.getDouble("price"));
                physicalCar.setColor(rs.getString("color"));
                physicalCar.setConceptualCarId(rs.getString("cc_id"));

                return physicalCar;
            }

            // Throw a data exception because we couldn't find the username with the given password
            throw new DataException("PhysicalCar with id '" + physicalCar.getId() + "' not found.");
        } finally {
            // Ensure we close the connection no matter what.
            stmt.close();
        }
    }
}
